﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Threading.Tasks;
using com.IronOne.BoardPACWinAppBO.Comment;
using com.IronOne.BoardPACWinAppDAO.DbOperations;
using com.IronOne.BoardPACWinAppDAO.DB;
using com.IronOne.BoardPACWinAppUtil.Enums;
using com.IronOne.BoardPACWinAppUtil.Util;
using SQLite;

namespace com.IronOne.BoardPACWinAppDAO.Comments
{
    public class CommentsDAO : ICommentsDAO
    {
        private readonly IDbOperations _dbOperations;

        public CommentsDAO()
        {
            _dbOperations = new DbOperations.DbOperations();
        }

        public async Task<ObservableCollection<CommentModel>> GetAllComments()
        {
            try
            {
                var conn = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                var sqlCommand = new SQLiteCommand(conn)
                {
                    CommandText =
                        "SELECT comments.CommentId,meeting.meetingId,meeting.meetingName as MeetingName,mainCategory.ShortName as MainCategory,subcategory.ShortName as SubCategory, comments.comment, " +
                        "(SELECT COUNT(Comments.meetingId) FROM Comments WHERE Comments.meetingId=meeting.meetingId " +
                        " GROUP BY Comments.meetingId) CommentCount, comments.type as CommentType, comments.commentedBy as CommentBy, " +
                        "comments.AddedDate as CommentDate, comments.memberId, comments.paperId," +
                        "CASE WHEN comments.type = 0 THEN (SELECT Paper.DocName FROM Paper WHERE Paper.paperId = comments.paperId)" +
                        "ELSE (SELECT Meeting.meetingName FROM Meeting WHERE comments.meetingId = meeting.meetingId)" +
                        "END as CommentTypeName, meeting.venue as MeetingVenue, comments.MemberId, comments.PaperId, meeting.Date as MeetingDate " +
                        "FROM comments INNER JOIN meeting ON comments.MeetingId = meeting.meetingId" +
                        " INNER JOIN subCategory ON meeting.subCategoryId = subcategory.SubCategoryId INNER JOIN mainCategory" +
                        " ON subCategory.categoryId = mainCategory.CategoryID"
                };


                List<CommentModel> commentList = null;

                Task commentListTask =
                    Task.Factory.StartNew(() => commentList = sqlCommand.ExecuteQuery<CommentModel>().ToList());
                await commentListTask;
                var commentsList = new ObservableCollection<CommentModel>(commentList);

                return commentsList;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                /*Fix for Database is locked exception*/
                //conn.Close();
                //conn.Dispose();
                GC.Collect();
            }
        }

        /// <summary>
        ///     Return comments model list according to comment type
        /// </summary>
        /// <param name="refId"></param>
        /// <param name="commentType"></param>
        /// <returns></returns>
        public async Task<ObservableCollection<CommentModel>> GetCommentTypeWiseComment(int refId, int commentType)
        {
            try
            {
                var conn = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                var sqlCommand = new SQLiteCommand(conn)
                {
                    CommandText =
                        "SELECT comments.CommentId,meeting.meetingId,meeting.meetingName as MeetingName,mainCategory.ShortName as MainCategory," +
                        "subcategory.ShortName as SubCategory, comments.comment, (SELECT COUNT(Comments.meetingId) FROM Comments WHERE  Comments.meetingId=meeting.meetingId  GROUP BY Comments.meetingId) CommentCount," +
                        "comments.type as CommentType, comments.commentedBy as CommentBy, comments.AddedDate as CommentDate, comments.memberId, comments.paperId, meeting.venue as MeetingVenue, comments.MemberId," +
                        "comments.PaperId, meeting.Date as MeetingDate, comments.PendingStatus, " +
                        "CASE WHEN comments.type = 0 THEN (SELECT Paper.DocName FROM Paper WHERE Paper.paperId = comments.paperId) " +
                        "ELSE (SELECT Meeting.meetingName FROM Meeting WHERE comments.meetingId = meeting.meetingId) " +
                        "END as CommentTypeName " +
                        "FROM comments INNER JOIN meeting ON comments.MeetingId = meeting.meetingId INNER JOIN subCategory ON meeting.subCategoryId = subcategory.SubCategoryId " +
                        "INNER JOIN mainCategory ON subCategory.categoryId = mainCategory.CategoryID " +
                        "WHERE comments.type = " + commentType +
                        (commentType == 0 ? " AND comments.PaperId=" + refId : " AND comments.MeetingId = " + refId)
                };

                //var hgh = commentType == 0 ? " AND comments.PaperId=" + refId : " AND comments.MeetingId = " + refId;

                List<CommentModel> commentList = null;

                Task commentListTask =
                    Task.Factory.StartNew(() => commentList = sqlCommand.ExecuteQuery<CommentModel>().ToList());
                await commentListTask;
                var commentsList = new ObservableCollection<CommentModel>(commentList);

                #region Commented

                //if (commentType == 0) //Paper Comments
                //{
                //    List<Log> logPaperCommentList = conn.Table<Log>().Where(l => l.PaperID == refId && l.Action.Equals(ApplicationConstants.LogActions.AddPaperComment)).ToList();

                //    foreach (var item in logPaperCommentList)
                //    {
                //        CommentModel cm = new CommentModel();
                //        cm.Comment = item.Data.Split(ApplicationConstants.Seperator)[0];
                //        if (item.Date != null)
                //        {
                //            cm.CommentDate = DateTime.Now;
                //            cm.CommentDateString = Utility.GetDate(cm.CommentDate).ToString(Global.DateFormat);
                //            cm.CommentDateString = cm.CommentDateString + " " + cm.CommentDate.ToString("hh:mm tt");
                //        }
                //        if (item.PaperID != null)
                //        {
                //            cm.PaperId = item.PaperID.Value; 
                //        }
                //        cm.CommentBy = Global.IPadDisplayName;
                //        cm.MemberId = Global.UserId;
                //        cm.CommentSyncedStatus = "Pending...";
                //        cm.CommentSyncingProgressVisibility = Visibility.Visible;

                //        commentsList.Add(cm);
                //    }
                //}
                //else 
                //{
                //    List<Log> logMeetingCommentList = conn.Table<Log>().Where(l => l.MeetingID == refId && l.Action.Equals(ApplicationConstants.LogActions.AddMeetingComment)).ToList();

                //    foreach (var item in logMeetingCommentList)
                //    {
                //        CommentModel cm = new CommentModel();
                //        cm.Comment = item.Data.Split(ApplicationConstants.Seperator)[0];
                //        if (item.Date != null)
                //        {
                //            cm.CommentDate = DateTime.Now;
                //            cm.CommentDateString = Utility.GetDate(cm.CommentDate).ToString(Global.DateFormat);
                //            cm.CommentDateString = cm.CommentDateString + " " + cm.CommentDate.ToString("hh:mm tt");
                //        }
                //        if (item.MeetingID != null)
                //        {
                //            cm.PaperId = item.MeetingID.Value;
                //        }
                //        cm.CommentBy = Global.IPadDisplayName;
                //        cm.MemberId = Global.UserId;
                //        cm.CommentSyncedStatus = "Pending...";
                //        cm.CommentSyncingProgressVisibility = Visibility.Visible;

                //        commentsList.Add(cm);
                //    }
                //} 

                #endregion

                return commentsList;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                /*Fix for Database is locked exception*/
                //conn.Close();
                //conn.Dispose();
                GC.Collect();
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="commentType">
        ///     Paper Or Meeting Comment / CommentType == 1 -> meetingComment, CommentType == 0 ->
        ///     paperComment
        /// </param>
        /// <param name="action"></param>
        /// <param name="comment"></param>
        /// <param name="refId"> MeetingID or PaperID </param>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public async Task<bool> AddComments(int commentType, string action, string comment, int refId, int meetingId)
        {
            try
            {
                var connAsync = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await connAsync.RunInTransactionAsync(connection =>
                {
                    try
                    {
                        connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                        var logEntity = new Log {MemberId = Global.UserId}; //Create log table record

                        if (commentType.Equals(0)) logEntity.PaperID = refId;
                        else logEntity.MeetingID = refId;

                        logEntity.Action = action; //TODO: set relevant value
                        logEntity.Data = comment;
                        logEntity.Date = DateTime.Now;
                        logEntity.UpdateID = Guid.NewGuid().ToString();
                        //logEntity.Info = ""; //TODO: set relevant value
                        //logEntity.Status = ""; //TODO: set relevant value
                        //logEntity.LastUpdatedDateTime = null;//TODO: set relevant value
                        //logEntity.LastUpdatedTimeStamp = null;//TODO: set relevant value
                        //logEntity.UpdateID = "1";//TODO : set relevant value
                        connection.Insert(logEntity);

                        var commentEntity = new DB.Comments
                        {
                            CommentId = (connection.Table<DB.Comments>().GroupBy(c => c.CommentId).Any())
                                ? connection.Table<DB.Comments>().Max(c => c.CommentId) + 1
                                : 1
                        };
                        if (commentType.Equals(0))
                        {
                            commentEntity.PaperId = refId;
                            commentEntity.MeetingId = meetingId;
                        }
                        else
                            commentEntity.MeetingId = refId;

                        commentEntity.MemberId = Global.UserId;
                        commentEntity.AddedDate = DateTime.Now;
                        commentEntity.Comment = comment.Split(ApplicationConstants.Seperator)[0];
                        commentEntity.CommentedBy = Global.IPadDisplayName;
                        commentEntity.type = commentType;
                        commentEntity.Status = 0; //is comment seen or not
                        commentEntity.PendingStatus = 1; //PendingStatus = 1 -> Comment not sync with backend

                        connection.Insert(commentEntity);
                        connection.Commit();
                    }
                    catch (Exception)
                    {
                        connection.Rollback();
                        throw;
                    }
                });
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> UpdateViewedComments(string action, List<int> meetingIds, List<int> paperIds)
        {
            try
            {
                var connAsync = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await connAsync.RunInTransactionAsync(connection =>
                {
                    try
                    {
                        connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                        List<Log> logList;
                        foreach (var meetingId in meetingIds)
                        {
                            var id = meetingId;
                            logList =
                                connection.Table<Log>()
                                    .Where(
                                        l =>
                                            l.Action.Equals(ApplicationConstants.LogActions.UpdateViewedMeetingComments) &&
                                            l.MeetingID.Equals(id))
                                    .ToList();
                            if (logList.Count == 0) //Check if record already exist in log table
                            {
                                var logEntity = new Log
                                {
                                    MemberId = Global.UserId,
                                    PaperID = 0,
                                    MeetingID = id,
                                    Action = action,
                                    Date = DateTime.Now,
                                    UpdateID = Guid.NewGuid().ToString()
                                };

                                connection.Insert(logEntity);
                            }

                            var commentEntity =
                                connection.Table<DB.Comments>()
                                    .Where(c => c.MeetingId.Equals(id) && c.Status.Equals(0) && c.type.Equals(1))
                                    .ToList();
                            foreach (var item in commentEntity)
                            {
                                item.Status = 1; //Comments viewed
                                connection.Update(item);
                            }
                        }

                        foreach (var paperId in paperIds)
                        {
                            var id = paperId;
                            logList =
                                connection.Table<Log>()
                                    .Where(
                                        l =>
                                            l.Action.Equals(ApplicationConstants.LogActions.UpdateViewedPaperComments) &&
                                            l.PaperID.Equals(id))
                                    .ToList();
                            if (logList.Count == 0)
                            {
                                var logEntity = new Log
                                {
                                    MemberId = Global.UserId,
                                    PaperID = id,
                                    MeetingID = 0,
                                    Action = action,
                                    Date = DateTime.Now,
                                    UpdateID = Guid.NewGuid().ToString()
                                };

                                connection.Insert(logEntity);
                            }

                            var commentEntity =
                                connection.Table<DB.Comments>()
                                    .Where(c => c.PaperId.Equals(id) && c.Status.Equals(0) && c.type.Equals(0))
                                    .ToList();
                            foreach (var item in commentEntity)
                            {
                                item.Status = 1; //Comments viewed
                                connection.Update(item);
                            }
                        }
                        connection.Commit();
                    }
                    catch (Exception)
                    {
                        connection.Rollback();
                        throw;
                    }
                });

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> GetPendingMeetingAndPaperComments()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var count =
                    await
                        conn.Table<Log>()
                            .Where(
                                x =>
                                    x.Action.Equals(ApplicationConstants.LogActions.AddMeetingComment) ||
                                    x.Action.Equals(ApplicationConstants.LogActions.AddPaperComment))
                            .CountAsync();
                return count;
            }
            catch (Exception)
            {
                return -1;
            }
        }
    }
}